![BigQueryのジョブのラベル機能が便利でした(小ネタ)](https://devio2023-media.developers.io/wp-content/uploads/2020/09/gcp-eyecatch-bigquery_1200x630.png)
BigQueryのジョブのラベル機能が便利でした(小ネタ)
データアナリティクス事業本部、池田です。
BigQueryでの作業をした際に、ジョブラベルがとても便利だと感じたのでブログにします。
ラベルについて
BigQueryでは 各リソースにラベルをつける
ことができるそうです。
ラベルの対象はデータセット
テーブル
ビュー
ジョブ
で、
今回はジョブに付与するラベルについて触れます。
ジョブラベルは他のリソースに付与するラベルよりも制限があり、 あとから変更できなかったり、タグに変換できなかったりします。 また、ラベル自体が課金対象となるようです。
ジョブにラベルを追加すると、そのラベルが課金データとしてカウントされます。
使ってみる
ジョブラベルはコンソールからのクエリ実行では付与できないので、
bqコマンド
の query
から実行してみます。
--label
オプションに「KEY:VALUE」の形で設定します。
こんな感じ↓
bq --location=US query \ --use_legacy_sql=false \ --use_cache=false \ --label "department:shipping" \ 'SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality`'
ラベルに利用部門を設定するイメージです。
今回は BigQueryの一般公開データセット
から出生データの natality
サンプルテーブルを参照しています。
↓ Cloud Shell から実行しました。
ジョブのIDを使って、 bqコマンド
の show
からジョブの情報を参照しラベルを表示できます。
(一番右に Labels
列があります。)
$ bq show -j --format=pretty {ジョブのID} +----------+---------+-----------------+----------------+------------------------------+-----------------+--------------+--------------+---------------------+ | Job Type | State | Start Time | Duration | User Email | Bytes Processed | Bytes Billed | Billing Tier | Labels | +----------+---------+-----------------+----------------+------------------------------+-----------------+--------------+--------------+---------------------+ | query | SUCCESS | 06 Apr 07:11:38 | 0:00:00.872000 | foo@bar | 0 | 0 | 0 | department:shipping | +----------+---------+-----------------+----------------+------------------------------+-----------------+--------------+--------------+---------------------+
また、 INFORMATION_SCHEMA
の JOBS_BY_* ビュー
にもラベルは含まれ、抽出条件としても使用できます。
【 INFORMATION_SCHEMA を使用したジョブ メタデータの取得 】
labels
カラムはRECORD型なので、 SAFE_OFFSET()
などで↓こんな感じで取得します。
SELECT labels[SAFE_OFFSET(0)].value AS dept, creation_time, job_id, start_time, end_time, REGEXP_REPLACE(LEFT(query, 100), '\r?\n', ' ') AS query FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time >= '2021-04-06 07:00:00' -- UTC, partition AND labels[SAFE_OFFSET(0)].key = 'department' ORDER BY creation_time DESC;
(部門情報を持つジョブを抽出するイメージ)
↓
+----------+---------------------+--------------------------------------------+---------------------+---------------------+---------------------------------------------------------------------+ | dept | creation_time | job_id | start_time | end_time | query | +----------+---------------------+--------------------------------------------+---------------------+---------------------+---------------------------------------------------------------------+ | shipping | 2021-04-06 07:11:37 | foo | 2021-04-06 07:11:38 | 2021-04-06 07:11:39 | SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality` | +----------+---------------------+--------------------------------------------+---------------------+---------------------+---------------------------------------------------------------------+
特に私が便利だと感じたのは、クエリのチューニングやパフォーマンスの確認で、 たくさんある結果を整理する時でした。
↓こんな感じでテスト内容が分かるようにラベルをつけておいて…
bq --location=US query \ --use_legacy_sql=false \ --use_cache=false \ --label "title:test1" \ --label "case:male" \ 'SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality` WHERE is_male IS true' bq --location=US query \ --use_legacy_sql=false \ --use_cache=false \ --label "title:test1" \ --label "case:not_male" \ 'SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality` WHERE is_male IS false'
↓こんな感じのクエリで所要時間やスロットの使われ方を確認する時に ラベルがあるとだいぶ探しやすく・見やすいです。
SELECT labels[SAFE_OFFSET(0)].value AS title, labels[SAFE_OFFSET(1)].value AS `case`, ROUND(TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) / 1000, 1) AS time_sec, ROUND(total_slot_ms / TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS avg_slots, total_slot_ms, total_bytes_billed, cache_hit, (error_result IS NOT NULL) AS has_error, creation_time, job_id, priority, start_time, end_time, state, total_bytes_processed, REGEXP_REPLACE(LEFT(query, 100), '\r?\n', ' ') AS query FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE creation_time >= '2021-04-06 07:00:00' -- UTC, partition AND labels[SAFE_OFFSET(0)].value LIKE 'test%' ORDER BY title, `case`;
↓
+-------+----------+----------+-----------+---------------+--------------------+-----------+-----------+---------------------+--------------------------------------------+-------------+---------------------+---------------------+-------+-----------------------+---------------------------------------------------------------------------------------------+ | title | case | time_sec | avg_slots | total_slot_ms | total_bytes_billed | cache_hit | has_error | creation_time | job_id | priority | start_time | end_time | state | total_bytes_processed | query | +-------+----------+----------+-----------+---------------+--------------------+-----------+-----------+---------------------+--------------------------------------------+-------------+---------------------+---------------------+-------+-----------------------+---------------------------------------------------------------------------------------------+ | test1 | male | 0.9 | 11.0 | 9869 | 138412032 | false | false | 2021-04-06 07:17:03 | foo | INTERACTIVE | 2021-04-06 07:17:03 | 2021-04-06 07:17:04 | DONE | 137826763 | SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality` WHERE is_male IS true | | test1 | not_male | 0.8 | 17.0 | 13818 | 138412032 | false | false | 2021-04-06 07:17:27 | bar | INTERACTIVE | 2021-04-06 07:17:27 | 2021-04-06 07:17:28 | DONE | 137826763 | SELECT COUNT(*) AS cnt FROM `bigquery-public-data.samples.natality` WHERE is_male IS false | +-------+----------+----------+-----------+---------------+--------------------+-----------+-----------+---------------------+--------------------------------------------+-------------+---------------------+---------------------+-------+-----------------------+---------------------------------------------------------------------------------------------+
state
カラムは PENDING, RUNNING, DONE
の状態だけ(エラーは無い)のようで、
クエリが成功しているのかどうかは error_result
カラムで確認するようにしています。
(試した限りはそれでたぶん判別できそう…)
おわりに
いろいろなクラウドのサービスがありますが、 タグやラベルの機能がある場合にそれを使っていると、 なんとなくおしゃれな感じがしませんでしょうか? 気のせいかもしれません。